from datetime import datetime, timedelta
import pandas as pd
import match  # 假设这是一个自定义模块
import time
import logging

from utils.verifyParameters import verify

# 配置日志
logging.basicConfig(level=logging.INFO)


def process_hubeihuaxiang_reconciliation(recon_start_time, recon_end_time, file_path, fault_tolerant, station_id, ignore_time):
    total_start_time = time.time()
    # region 配置
    station_config = {
        "name": "湖北华祥",
        "ids": [226],
        "main_body_gas_station": 131,
        "file_keyword": "(.*)湖北华祥(.*)",
        "columns": [{
                        "create_time": "时间",
                        "gas_num": "销售",
                        "gas_price": "单价",
                    }, {
                        "create_time": "订单时间",
                        "gas_num": "数量",
                        "gas_price": "单价",
                    }],
        "diff_num": 1,  # 差异在x公斤以内的设置为疑似匹配
        "excel_get_time_range": True
    }

    verify(fault_tolerant, station_id, ignore_time, station_config)

    # 时间处理
    time_process_start = time.time()
    start_time_dt = datetime.strptime(recon_start_time, "%Y-%m-%d %H:%M:%S") - timedelta(hours=1)
    start_time = start_time_dt.strftime("%Y-%m-%d %H:%M:%S")
    end_time_dt = datetime.strptime(recon_end_time, "%Y-%m-%d %H:%M:%S") + timedelta(hours=1)
    end_time = end_time_dt.strftime("%Y-%m-%d %H:%M:%S")
    time_process_duration = time.time() - time_process_start
    logging.info(f"时间处理耗时: {time_process_duration:.2f}秒")

    # step 1 获取Excel中的可用sheet
    step1_start_time = time.time()
    closest_sheet = None
    min_diff = None
    closest_sheet_name = None

    try:
        excel_file = pd.ExcelFile(file_path)
    except Exception as e:
        print(f"无法加载Excel文件: {e}")
        return

    sheet_names = excel_file.sheet_names
    for sheet in sheet_names:
        try:
            df_tmp = pd.read_excel(file_path, sheet_name=sheet)
            # 只在 columns 是 list 的时候执行匹配逻辑
            if isinstance(station_config["columns"], list):
                # 遍历 columns 配置
                for config in station_config["columns"]:
                    # 先检查 DataFrame 表头是否包含该配置
                    if all(item in df_tmp.columns.tolist() for item in config.values()):
                        station_config["columns"] = config  # 替换为匹配到的配置
                        break

                    # 否则在前 13 行里找
                    for row in df_tmp.head(13).itertuples():
                        if all(item in list(row[1:]) for item in config.values()):
                            station_config["columns"] = config  # 替换为匹配到的配置
                            break

                # 如果一个都没匹配到，报错
                if isinstance(station_config["columns"], list):
                    continue
        except Exception as e:
            print(f"读取sheet {sheet} 失败: {e}")
            continue

        header_row = None
        if all(item in df_tmp.columns.tolist() for item in list(station_config["columns"].values())):
            header_row = -1
        else:
            for row in df_tmp.head(3).itertuples():
                if all(item in list(row[1:]) for item in list(station_config["columns"].values())):
                    header_row = row.Index
                    break

        if header_row is not None:
            try:
                df = pd.read_excel(file_path, sheet_name=sheet, skiprows=header_row + 1)
                df[station_config["columns"]["create_time"]] = pd.to_datetime(
                    df[station_config["columns"]["create_time"]], errors="coerce"
                )
                df = df.dropna(subset=[station_config["columns"]["create_time"]])
                df = df.dropna(subset=[station_config["columns"]["gas_num"]])
                latest_time = df[station_config["columns"]["create_time"]].max()
                if pd.notnull(latest_time):
                    time_diff = abs(
                        (latest_time.date() - datetime.strptime(recon_end_time, "%Y-%m-%d %H:%M:%S").date()).days)
                    if min_diff is None or time_diff < min_diff:
                        min_diff = time_diff
                        closest_sheet = df
                        closest_sheet_name = sheet
            except Exception as e:
                print(f"处理sheet {sheet} 时出错: {e}")

    if closest_sheet is None:
        print("未找到有效sheet")
        return

    try:
        station_data = closest_sheet[list(station_config["columns"].values())]
        station_data = match.set_station_id_column(station_data, station_config)

    except KeyError:
        print('配置文件与Excel不一致')
        return

    step1_duration = time.time() - step1_start_time
    logging.info(f"步骤1(获取Excel中的可用sheet)耗时: {step1_duration:.2f}秒")

    print(len(station_data), closest_sheet_name)
    station_dfs_dict = {}

    # step 2 处理Excel
    step2_start_time = time.time()
    if "station_name" in station_config["columns"] and len(station_config["ids"]) > 1 and "nms" in station_config:
        station_names = station_config["nms"]
        station_name_col = station_config["columns"]["station_name"]
        for idx, pattern in enumerate(station_names):
            matched_df = station_data[station_data[station_name_col].astype(str).str.match(pattern, na=False)]
            non_midnight_count = (
                    matched_df[station_config["columns"]["create_time"]].dt.strftime("%H:%M:%S") != "00:00:00"
            ).sum()
            if len(matched_df) > 0 and (non_midnight_count / len(matched_df)) > 0.5:
                matched_df = matched_df.sort_values(by=station_config["columns"]["create_time"])
            station_dfs_dict[station_config["ids"][idx]] = matched_df

    step2_duration = time.time() - step2_start_time
    logging.info(f"步骤2(处理Excel数据)耗时: {step2_duration:.2f}秒")

    match_result = []

    # step 3 对比数据
    step3_start_time = time.time()
    if station_dfs_dict:
        for station_id, df in station_dfs_dict.items():
            online_data = match.get_online_data([station_id], start_time, end_time)
            match_result.append(match.match_data_v1(df, online_data, station_config))
    else:
        if station_config.get("excel_get_time_range", False):
            station_data = station_data[
                station_data[station_config["columns"]["create_time"]].between(start_time, end_time)
            ]
        online_data = match.get_online_data(station_config['ids'], start_time, end_time)
        match_result.append(match.match_data_v1(station_data, online_data, station_config))

    step3_duration = time.time() - step3_start_time
    logging.info(f"步骤3(数据对比)耗时: {step3_duration:.2f}秒")

    # 生成JSON结果
    step4_start_time = time.time()
    json_result = match.create_json_result_v2(match_result, station_config, start_time, end_time)
    step4_duration = time.time() - step4_start_time
    logging.info(f"步骤4(生成JSON结果)耗时: {step4_duration:.2f}秒")

    total_duration = time.time() - total_start_time
    logging.info(f"湖北华祥对账单处理总耗时: {total_duration:.2f}秒")

    return json_result
